# 温度和湿度分别为一个topic版本
import time
import sys
import mysql.connector
import datetime
import paho.mqtt.client


# 定义数据库配置
config = {
    'user': 'talon',
    'password': 'xhwykzv',
    'host': 'localhost',
    'database': 'talon'
}

# MQTT订阅信息
mqttServerIP = "mq.tongxinmao.com"
mqttServerPort = 18830
mqttTopicTemp = "/talon/mouse/temp"
mqttTopicHumi = "/talon/mouse/humi"

# 全局变量
humi = 0
temp = 0


class Database:
    def __init__(self, config):
        try:
            self.cnx = mysql.connector.connect(**config)
            print("数据库连接成功\r\n")
        except mysql.connector.Error as err:
            print('连接失败：' + str(err))

    def execute(self, query, data=None):
        cursor = self.cnx.cursor()
        try:
            cursor.execute(query, data)
            rowcount = cursor.rowcount
            self.cnx.commit()
            cursor.close()
            return rowcount
        except mysql.connector.Error as err:
            self.cnx.rollback()
            cursor.close()
            print('执行失败：' + str(err))
            return None

    def close(self):
        self.cnx.close()
        print('连接已关闭。')


# 连接数据库
db = Database(config)


def on_connect(client, userdata, flags, rc):
    print("MQTT connected with result code "+str(rc))  # 打印连接状态
    client.subscribe(mqttTopicTemp)  # 订阅温度
    client.subscribe(mqttTopicHumi)  # 订阅湿度


def on_disconnect(client, userdata, rc):
    global db
    if rc != 0:
        print("Unexpected disconnection.")
        db.close()


def on_message(client, userdata, msg):
    global db
    global humi
    global temp
    print(msg.topic + ":" + str(msg.payload.decode('utf-8')))  # 打印接受的消息

    data = str(msg.payload)[2:-1]

    if msg.topic == mqttTopicTemp:
        temp = data
        # print(temp)
    elif msg.topic == mqttTopicHumi:
        humi = data
        # print(humi)
        nowtime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        # 定义插入语句和数据
        sql = "INSERT INTO sensor (time, temperature, humidity, mode) VALUES (%s, %s, %s, %s)"
        data = (nowtime, temp, humi, 0)
        # 执行插入操作
        result = db.execute(sql, data)
        if result is not None:
            print(f'成功插入{result}条记录。')
        else:
            db = Database(config)


# 连接MQTT
mqtt = paho.mqtt.client
# time.strftime('%Y%m%d%H%M%S',time.localtime(time.time()))
client_id = "TalonsIot_Server"
client = mqtt.Client(client_id)    # ClientId不能重复，所以使用当前时间
# client.username_pw_set("", "")  # 根据服务器要求是否设置
client.on_connect = on_connect
client.on_message = on_message
client.on_disconnect = on_disconnect
client.connect(mqttServerIP, mqttServerPort, 60)
client.loop_forever()
